package cn.wolfcode.cms.util;

import cn.wolfcode.cms.domain.Product;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {
    public static void export(HttpServletResponse response) {
        // 文件名
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = sdf.format(new Date()) + ".xls";
        response.setContentType("application/x-excel");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);// excel文件名

        List<Product> list = new ArrayList<>();
        WritableWorkbook workbook = null;
        Product product = null;

        try {
            // 创建一个excle对象
            workbook = Workbook.createWorkbook(response.getOutputStream());
            // 通过excle对象创建一个选项卡对象
            WritableSheet sheet = workbook.createSheet("商品列表", 0);
            //创建单元格
            String[] header = new String[]{"商品名称", "商品编码", "成本价", "销售价", "积分",
                    "规格", "图片路径", "一级分类", "二级分类", "备注"};
            Label label = null;
            for (int i = 0; i < header.length; i++) {
                label = new Label(i, 0, header[i]);
                sheet.addCell(label);

            }
            // 创建一个单元格对象 列 行 值
            for (int i = 1; i < list.size() + 1; i++) {
                label = new Label(0, i, list.get(i - 1).getName());
                label = new Label(1, i, list.get(i - 1).getSn());
                label = new Label(2, i, list.get(i - 1).getCostPrice().toString());
                label = new Label(3, i, list.get(i - 1).getSalePrice().toString());
                label = new Label(4, i, list.get(i - 1).getIntegral());
                label = new Label(5, i, list.get(i - 1).getSpecification());
                label = new Label(6, i, list.get(i - 1).getImgPath());
                label = new Label(7, i, list.get(i - 1).getFirstLevel());
                label = new Label(8, i, list.get(i - 1).getSecondLevel());
                label = new Label(9, i, list.get(i - 1).getRemark());
                label = new Label(10, i, list.get(i - 1).getImgPath());
                // 将创建好的单元格对象放入选项卡中
                sheet.addCell(label);
            }
            // 写入目标路径
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (WriteException | IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 针对product类进行导入的操作
     *
     * @return
     */
    /*public List<Product> excleIn() {
        List<Product> list = new ArrayList<>();
        Product product = null;
        Workbook workbook = null;
        try {
            // 获取Ecle对象
            workbook = Workbook.getWorkbook(new File("d:/product.xls"));
            // 获取选项卡对象 第0个选项卡
            Sheet sheet = workbook.getSheet(0);
            // 循环选项卡中的值
            for (int i = 0; i < sheet.getRows(); i++) {
                product = new Product();
                // 获取单元格对象
                Cell cell0 = sheet.getCell(0, i);
                // 取得单元格的值,并设置到对象中
                product.setId(Long.valueOf(cell0.getContents()));
                // 获取单元格对象，然后取得单元格的值,并设置到对象中
                product.setName(sheet.getCell(1, i).getContents());
                product.setSn(sheet.getCell(2, i).getContents());
                Cell cell1 = sheet.getCell(3, i);
                product.setCostPrice(new BigDecimal(cell1.getContents()));
                Cell cell2 = sheet.getCell(4, i);
                product.setSalePrice(new BigDecimal(cell2.getContents()));
                product.setIntegral(sheet.getCell(5, i).getContents());
                product.setSpecification(sheet.getCell(6, i).getContents());
                product.setImgPath(sheet.getCell(7, i).getContents());
                product.setFirstLevel(sheet.getCell(8, i).getContents());
                product.setSecondLevel(sheet.getCell(9, i).getContents());
                product.setRemark(sheet.getCell(10, i).getContents());
                list.add(product);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.close();
        }
        return list;
    }*/
}
